import pandas as pd
import pymysql
import numpy as np
from config import *

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3386,
            charset='utf8'
        )
    
    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
            SELECT LEFT(u.id_no, 4) as city_code, DATE_FORMAT(o.create_time, '%Y-%m') as month, count(u.id) as visitor_count FROM ticket_order_user_rel u 
            JOIN ticket_order o on o.id = u.order_id WHERE LENGTH(u.id_no) = 18 and o.pay_time is not null and o.pay_time != '' GROUP BY city_code, month
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        
        # Process the data
        city_DICT = {}  
        new_list = []
        for item in ret:
            new_list.append({
                'month': item['month'],
                'vision_count': item['vision_count'], 
                'city_name': city_DICT[item['city_code']]
            })
        
        df_city_monthly = pd.DataFrame(new_list)
        df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month']+'-01')
        
        def calculate_baseline(df, current_month, window_size=6):
            df_current = df[df['month'] == current_month] 
            history_start = current_month - pd.DateOffset(months=window_size)
            df_history = df[(df['month'] > history_start) & (df['month'] < current_month)]
            df_baseline = df_history.groupby('city_name')['vision_count'].agg(['mean', 'std']).reset_index()
            df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)
            df_merged = df_current.merge(df_baseline, on='city_name', how='left')
            return df_merged
            
        current_month = pd.to_datetime('2024-12-01')
        df_merged = calculate_baseline(df_city_monthly, current_month)
        df_merged['z_score'] = (df_merged['vision_count'] - df_merged['hist_mean']) / df_merged['hist_std']
        df_increased = df_merged[df_merged['z_score'] > 3]
        df_reduced = df_merged[df_merged['z_score'] < -3]
        print(df_increased)
        print("-----------------------------------------")
        print(df_reduced)
        

if __name__ == '__main__': 
    mu = MysqlUtils()
    mu.get_scenic_data()